﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using X.DALSqlServer;

namespace E.DAL
{
    public class CourseDAL
    {
        /// <summary>
        /// 获取课程类别列表
        /// </summary>
        /// <returns></returns>
        public IList<CourseCategoryModel> GetCourseCategoryList()
        { 
            IList<CourseCategoryModel> list = new List<CourseCategoryModel>();
            string sql = "SELECT ID,Name FROM E_Dict_CourseCategory";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    CourseCategoryModel model = new CourseCategoryModel();
                    model.ID = (int)dr["ID"];
                    model.Name = dr["Name"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取认证列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetCertificateList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Certificate WHERE IsPublic = 1 AND IsInternalPublic = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取项目列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetProjectList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Certificate WHERE IsPublic = 0 AND IsInternalPublic = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训公共项目列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetInternalPublicProjectList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Certificate WHERE IsPublic = 0 AND IsInternalPublic = 1";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取认证和项目列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetAllCertificateAndProjectList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Certificate";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }


        /// <summary>
        /// 获取课程列表
        /// </summary>
        /// <param name="certificateid"></param>
        /// <returns></returns>
        public Dictionary<int, string> GetRelationCourseList(int certificateid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = @"SELECT     a.ID AS RelationID, b.Name
                           FROM         E_Dict_Course2Certificate AS a LEFT OUTER JOIN
                                                 E_Dict_Course AS b ON a.CourseID = b.ID
                           WHERE     (a.CertificateID = " + certificateid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取证书相关课程列表
        /// </summary>
        /// <param name="certificateID"></param>
        /// <returns></returns>
        public Dictionary<int, string> GetCourseListByCertificateID(int certificateID)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = @"SELECT     a.CourseID, b.Name
                           FROM         E_Dict_Course2Certificate AS a LEFT OUTER JOIN
                                                 E_Dict_Course AS b ON a.CourseID = b.ID
                           WHERE     (a.CertificateID = " + certificateID + ") AND (b.DelFlag = 0)";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取教师所属项目相关课程列表
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public Dictionary<int, string> GetCourseListByTeacherID(int teacherid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT c.ID, c.Name FROM E_Teacher AS a INNER JOIN E_Dict_Course2Certificate AS b ON a.CertificateID = b.CertificateID INNER JOIN E_Dict_Course AS c ON b.CourseID = c.ID WHERE (a.ID = " + teacherid + ") AND (c.DelFlag = 0)";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取课程列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetCourseList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Course WHERE IsPublic = 1 AND IsInternalPublic = 0 AND DelFlag = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训课程列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetInternalCourseList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Course WHERE IsPublic = 0 AND IsInternalPublic = 0 AND DelFlag = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训公共课程列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetInternalPublicCourseList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Course WHERE IsPublic = 0 AND IsInternalPublic = 1 AND DelFlag = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取所有公共课程列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetAllPublicCourseList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Course WHERE (IsPublic = 1) OR (IsPublic = 0) AND (IsInternalPublic = 1) AND (DelFlag = 0)";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训课程列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetAllCourseList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Course WHERE DelFlag = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取认证相关课程ID列表
        /// </summary>
        /// <param name="certificateid"></param>
        /// <returns></returns>
        public IList<int> GetCourseIDListByCertificateID(int certificateid)
        {
            IList<int> list = new List<int>();
            string sql = "SELECT CourseID FROM E_Dict_Course2Certificate WHERE (CertificateID = " + certificateid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0]);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取认证名称
        /// </summary>
        /// <param name="certificateid"></param>
        /// <returns></returns>
        public string GetCertificateNameByID(int certificateid)
        {
            string sql = "SELECT Name FROM E_Dict_Certificate WHERE ID = " + certificateid;
            return SqlHelper.ExecuteScalarBySql(sql).ToString();
        }

        /// <summary>
        /// 获取认证或项目信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CertificateModel GetCertificateProjectInfoById(int id)
        {
            CertificateModel model = new CertificateModel();
            string sql = "SELECT ID,Name,IsPublic FROM E_Dict_Certificate WHERE ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.Name = dr["Name"].ToString();
                    model.IsPublic = (bool)dr["IsPublic"];
                }
                else
                    model.ID = -1;
                return model;
            }
        }

        /// <summary>
        /// 添加认证或项目
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddCertificateProject(CertificateModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@IsPublic", model.IsPublic),
                new SqlParameter("@IsInternalPublic", model.IsInternalPublic)
            };
            string sql = "INSERT INTO E_Dict_Certificate(Name,IsPublic,IsInternalPublic)VALUES(@Name,@IsPublic,@IsInternalPublic)";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 检查认证或项目名称是否重复
        /// </summary>
        /// <param name="coursename"></param>
        /// <param name="selfid"></param>
        /// <returns></returns>
        public bool CheckExistProjectName(string name, int selfid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", name),
                new SqlParameter("@SelfID", selfid)
            };
            string sql = "SELECT ID FROM E_Dict_Certificate WHERE Name = @Name ";
            if (selfid != -1)
            {
                sql += " AND ID <> @SelfID";
            }
            return SqlHelper.Exists(sql, parms);
        }

        /// <summary>
        /// 更新认证或项目信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateCertificateProject(CertificateModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Name", model.Name)
            };
            string sql = "UPDATE E_Dict_Certificate SET Name = @Name WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取课程列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public IList<ShortCourseModel> GetPublicCourseList(SearchCourseModel searchModel)
        {
            IList<ShortCourseModel> list = new List<ShortCourseModel>();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", searchModel.Name),
                new SqlParameter("@CnPID", searchModel.CnPID),
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getPublicCourseList", parms))
            {
                while (dr.Read())
                {
                    ShortCourseModel shortModel = new ShortCourseModel();
                    shortModel.ID = (int)dr["ID"];
                    shortModel.Name = dr["Name"].ToString();
                    shortModel.CategoryName = dr["CategoryName"].ToString();
                    list.Add(shortModel);
                }
                return list;
            }
        }

        /// <summary>
        /// 获取公共课程信息
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public CourseModel GetPublicCourseInfoById(int courseid)
        {
            CourseModel model = new CourseModel();
            string sql = "SELECT a.ID, a.Name, a.CategoryID, a.SchoolHour, a.Description, b.Name AS CategoryName FROM E_Dict_Course AS a INNER JOIN E_Dict_CourseCategory AS b ON a.CategoryID = b.ID WHERE a.ID = " + courseid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.Name = dr["Name"].ToString();
                    model.CategoryID = (int)dr["CategoryID"];
                    model.CategoryName = dr["CategoryName"].ToString();
                    model.SchoolHour = (decimal)dr["SchoolHour"];
                    model.Description = dr["Description"] is DBNull ? "&nbsp;" : dr["Description"].ToString();
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 添加公共课程
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddPublicCourse(CourseModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@SchoolHour", model.SchoolHour),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@Description", model.Description),
                new SqlParameter("@IsPublic", model.IsPublic)
            };
            string sql = "INSERT INTO E_Dict_Course(CategoryID,Name,SchoolHour,Description,IsPublic)VALUES(@CategoryID,@Name,@SchoolHour,@Description,@IsPublic)";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 更新公共课程信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdatePublicCourse(CourseModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@SchoolHour", model.SchoolHour),
                new SqlParameter("@Description", model.Description),
            };
            string sql = "UPDATE E_Dict_Course SET Name = @Name, CategoryID = @CategoryID, SchoolHour = @SchoolHour, Description = @Description WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 编辑认证与课程关系
        /// </summary>
        /// <param name="certificateid"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int EditRelation(int certificateid, string[] courseid)
        {
            IList<string> sqllist = new List<string>();
            Certificate2CourseHandle(certificateid, courseid, sqllist);
            return SqlHelper.ExecuteSqlTran(sqllist);
        }

        /// <summary>
        /// 认证与课程关系关系处理
        /// </summary>
        /// <param name="id"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public void Certificate2CourseHandle(int id, string[] courseid, IList<string> sqllist)
        {
            sqllist.Add("DELETE FROM E_Dict_Course2Certificate WHERE CertificateID = " + id);
            foreach (string s in courseid)
            {
                sqllist.Add("INSERT INTO E_Dict_Course2Certificate(CertificateID, CourseID)VALUES(" + id + "," + s + ");");
            }
        }

        /// <summary>
        /// 激活课程
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int ActiveCourse(int courseid)
        {
            string sql = "UPDATE E_Dict_Course SET DelFlag = 0 WHERE ID = " + courseid;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 冻结课程
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int FreezeCourse(int courseid)
        {
            string sql = "UPDATE E_Dict_Course SET DelFlag = 1 WHERE ID = " + courseid;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        #region 内训公共课

        /// <summary>
        /// 检查课程名称是否重复
        /// </summary>
        /// <param name="coursename"></param>
        /// <param name="pid"></param>
        /// <param name="selfid"></param>
        /// <returns></returns>
        public bool CheckExistPublicCourseName(string coursename, int pid, int selfid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CourseName", coursename),
                new SqlParameter("@Pid", pid),
                new SqlParameter("@SelfID", selfid)
            };
            string sql = "SELECT ID FROM v_publicproject2course WHERE Name = @CourseName AND CnPID = @Pid ";
            if (selfid != -1)
            {
                sql += " AND ID <> @SelfID";
            }
            return SqlHelper.Exists(sql, parms);
        }


        /// <summary>
        /// 获取内训公共课程列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public CourseListModel GetInternalPublicCourseListByCondition(SearchCourseModel searchModel, int pageSize, int pageIndex)
        {
            CourseListModel listModel = new CourseListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", searchModel.Name),
                new SqlParameter("@CnPID", searchModel.CnPID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortCourseModel> list = new List<ShortCourseModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getInternalPublicCourseListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortCourseModel shortModel = new ShortCourseModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.Name = dr["Name"].ToString();
                        shortModel.CnPName = dr["CnPName"].ToString();
                        shortModel.DelFlag = (bool)dr["DelFlag"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取课件列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public CourseWareListModel GetInternalPublicCourseWareListByCondition(SearchCourseWareModel searchModel, int pageSize, int pageIndex)
        {
            CourseWareListModel listModel = new CourseWareListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@ProjectID", searchModel.ProjectID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortCourseWareModel> list = new List<ShortCourseWareModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getPublicCourseWareListByCondition_2", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortCourseWareModel shortModel = new ShortCourseWareModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.CourseName = dr["CourseName"].ToString();
                        shortModel.ProjectName = dr["ProjectName"].ToString();
                        shortModel.SerialNumber = (int)dr["SerialNumber"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取所有公共课程项目列表及作为公共课程的旗舰认证
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetAllInternalPublicProjectList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Certificate WHERE (ID = 1) OR (IsInternalPublic = 1)";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        #endregion


        #region 内训

        /// <summary>
        /// 检查课程名称是否重复
        /// </summary>
        /// <param name="coursename"></param>
        /// <param name="pid"></param>
        /// <param name="selfid"></param>
        /// <returns></returns>
        public bool CheckExistCourseName(string coursename, int pid, int selfid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CourseName", coursename),
                new SqlParameter("@Pid", pid),
                new SqlParameter("@SelfID", selfid)
            };
            string sql = "SELECT ID FROM v_project2course WHERE Name = @CourseName AND CnPID = @Pid ";
            if (selfid != -1)
            {
                sql += " AND ID <> @SelfID";
            }
            return SqlHelper.Exists(sql, parms);
        }


        /// <summary>
        /// 获取老师项目组信息
        /// </summary>
        /// <param name="teacherid"></param>
        /// <returns></returns>
        public Dictionary<int, string> GetTeacherProjectList(int teacherid)
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT a.CertificateID, b.Name FROM E_Teacher a INNER JOIN E_Dict_Certificate b ON a.CertificateID = b.ID WHERE a.ID = " + teacherid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训项目列表
        /// </summary>
        /// <returns></returns>
        public Dictionary<int, string> GetInternalProjectList()
        {
            Dictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Dict_Certificate WHERE IsPublic = 0 AND IsInternalPublic = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取内训课程信息
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public CourseModel GetInternalCourseInfoById(int courseid)
        {
            CourseModel model = new CourseModel();
            string sql = "SELECT a.ID, a.Name, a.SchoolHour, a.Description, a.IsPublic, a.NeedLearn, c.Name AS CnPName, a.IsNeedJoinClass, c.ID AS CnPID, a.SkillGroup as SkillGroupId FROM E_Dict_Course AS a INNER JOIN E_Dict_Course2Certificate AS b ON a.ID = b.CourseID INNER JOIN E_Dict_Certificate AS c ON b.CertificateID = c.ID WHERE (a.ID = " + courseid + ")";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.Name = dr["Name"].ToString();
                    model.CnPID = (int)dr["CnPID"];
                    model.CnPName = dr["CnPName"].ToString();
                    model.SchoolHour = (decimal)dr["SchoolHour"];
                    model.Description = dr["Description"] is DBNull ? "&nbsp;" : dr["Description"].ToString();
                    model.IsPublic = (bool)dr["IsPublic"];
                    model.NeedLearn = (bool)dr["NeedLearn"];
                    model.IsNeedJoinClass = (bool)dr["IsNeedJoinClass"];
                    model.SkillGroup = dr["SkillGroupId"] == null ? -1 : (int)dr["SkillGroupId"];
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 获取课程列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public CourseListModel GetInternalCourseListByCondition(SearchCourseModel searchModel, int pageSize, int pageIndex)
        {
            CourseListModel listModel = new CourseListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", searchModel.Name),
                new SqlParameter("@CnPID", searchModel.CnPID),
                new SqlParameter("@DelFlag", searchModel.DelFlag),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortCourseModel> list = new List<ShortCourseModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getInternalCourseListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortCourseModel shortModel = new ShortCourseModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.Name = dr["Name"].ToString();
                        shortModel.CnPName = dr["CnPName"].ToString();
                        shortModel.DelFlag = (bool)dr["DelFlag"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 添加内训课程
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddInternalCourse(CourseModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@SchoolHour", model.SchoolHour),
                new SqlParameter("@CategoryID", model.CategoryID),
                new SqlParameter("@Description", model.Description),
                new SqlParameter("@IsPublic", model.IsPublic),
                new SqlParameter("@IsInternalPublic", model.IsInternalPublic),
                new SqlParameter("@NeedLearn", model.NeedLearn),
                new SqlParameter("@IsNeedJoinClass",model.IsNeedJoinClass),
                new SqlParameter("@SkillGroup", model.SkillGroup),
                new SqlParameter("@PrecLevelCourseId",model.PrecLevelCourseId)
            };
            string sql = "INSERT INTO E_Dict_Course(CategoryID,Name,SchoolHour,Description,IsPublic,NeedLearn,IsInternalPublic,IsNeedJoinClass,SkillGroup,PrecLevelCourseId)VALUES(@CategoryID,@Name,@SchoolHour,@Description,@IsPublic,@NeedLearn,@IsInternalPublic,@IsNeedJoinClass,@SkillGroup,@PrecLevelCourseId); SELECT @@IDENTITY";
            int id = -1;
            int rst = 0;
            try
            {
                id = Convert.ToInt32(SqlHelper.ExecuteScalarBySql(sql, parms));
                sql = "INSERT INTO E_Dict_Course2Certificate(CourseID,CertificateID)VALUES(" + id + "," + model.CnPID + ")";
                rst = SqlHelper.ExecuteNonQueryBySql(sql);
            }
            catch (Exception e)
            {
                if (id != -1)
                {
                    sql = "DELECT FROM E_Dict_Course WHERE ID = " + id;
                    rst = SqlHelper.ExecuteNonQueryBySql(sql);
                }
            }
            return rst;
        }

        /// <summary>
        /// 更新内训课程信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateInternalCourse(CourseModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@SchoolHour", model.SchoolHour),
                new SqlParameter("@Description", model.Description),
                new SqlParameter("@IsNeedJoinClass", model.IsNeedJoinClass),
                new SqlParameter("@SkillGroup", model.SkillGroup)
            };
            string sql = "UPDATE E_Dict_Course SET Name = @Name, SchoolHour = @SchoolHour, Description = @Description, IsNeedJoinClass = @IsNeedJoinClass, SkillGroup=@SkillGroup WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取内训自选课
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public InternalFreeCourseListModel GetInternalFreeCourseListByCondition(InternalFreeCourseSearchModel searchModel, int pageSize, int pageIndex)
        {
            InternalFreeCourseListModel listModel = new InternalFreeCourseListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@ProjectID", searchModel.ProjectID),
                new SqlParameter("@DelFlag", searchModel.DelFlag),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex),
                new SqlParameter("@SkillGroup",searchModel.SkillGroup)
            };
            IList<InternalFreeCourseModel> list = new List<InternalFreeCourseModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getFreeCourseListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        InternalFreeCourseModel model = new InternalFreeCourseModel();
                        model.ID = (int)dr["ID"];
                        model.Name = dr["CourseName"].ToString();
                        model.ProjectId = (int)dr["ProjectID"];
                        model.ProjectName = dr["ProjectName"].ToString();
                        list.Add(model);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 自选课程报名
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="classid"></param>
        /// <param name="courseid"></param>
        /// <param name="projectid"></param>
        /// <returns></returns>
        public int SignUpInternalFreeCourse(int userid, int courseid, int projectid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@CourseID", courseid),
                new SqlParameter("@ProjectID", projectid)
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_internal_signupcourse_byfree", parms);
        }

        /// <summary>
        /// 判断是否有需要在学习courseid的课程之前要必须完成的课程
        /// </summary>
        /// <param name="courseid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public bool IsNeedFinishPrevCourse(int courseid, int userid)
        {
            bool isNeed = false;
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@CourseID", courseid)
            };
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("usp_isNeedFinishPrevCourse", parms))
            {
                while (dr.Read())
                {
                    isNeed = (bool)dr["isNeedFinish"];
                }
            }
            return isNeed;
        }

        #endregion

        #region 技能组


        /// <summary>
        /// 判断新建的技能组名称是否已经存在
        /// </summary>
        /// <param name="skillGroupName"></param>
        /// <returns></returns>
        public bool IsExistSkillGroupName(string skillGroupName)
        {
            bool isExist = false;
            string sql = "select count(ID) as SkillGroupCount from E_SkillGroup where SkillGroupName=@SkillGroupName";
            IDataParameter[] parms = new IDataParameter[]{
                new SqlParameter("@SkillGroupName", skillGroupName.Trim())
            };

            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql, parms))
            {
                while (dr.Read())
                {
                    if (((int)dr["SkillGroupCount"]) > 0)
                    {
                        isExist = true;
                    }
                }
            }
            return isExist;

        }

        /// <summary>
        /// 通过Id获得技能组
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SkillGroup GetSkillGroupModel(int id)
        {
            SkillGroup model = new SkillGroup();
            string sql = "select Id, SkillGroupName from E_SkillGroup where Id = @Id";
            IDataParameter[] parms = new IDataParameter[]{
                new SqlParameter("@Id",id)
            };

            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql, parms))
            {
                while (dr.Read())
                {
                    model.ID = (int)dr["Id"];
                    model.Name = dr["SkillGroupName"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 增加新技能组
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddSkillGroup(SkillGroup model)
        {
            string tableName = "E_SkillGroup";

            int maxId = SqlHelper.GetMaxTableId(tableName);
            int id = maxId + 1;
            string sql = "insert into E_SkillGroup(Id, SkillGroupName) values(@Id, @SkillGroupName)";
            IDataParameter[] parms = new IDataParameter[]{
                new SqlParameter("@Id", id),
                new SqlParameter("@SkillGroupName", model.Name)
            };

            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }


        /// <summary>
        /// 更新技能组
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateSkillGroup(SkillGroup model)
        {
            string sql = "update E_SkillGroup set SkillGroupName=@SkillGroupName where Id=@Id";
            IDataParameter[] parms = new IDataParameter[]{
                new SqlParameter("@Id",model.ID),
                new SqlParameter("@SkillGroupName", model.Name)
            };

            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获得所有技能组
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public SkillGroupListModel GetSkillGroupList(string name)
        {
            SkillGroupListModel listModel = new SkillGroupListModel();
            IList<SkillGroup> list = new List<SkillGroup>();
            StringBuilder sql = new StringBuilder("select Id, SkillGroupName from E_SkillGroup where 1=1");
            if(name != null && !"".Equals(name.Trim()))
            {
                sql.Append(" and SkillGroupName='" + name + "'");
            }


            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql.ToString()))
            {
                while (dr.Read())
                {
                    SkillGroup model = new SkillGroup();
                    model.ID = (int)dr["ID"];
                    model.Name = dr["SkillGroupName"].ToString();
                    list.Add(model);
                }
                listModel.TotalCount = list.Count;
                listModel.List = list;
                return listModel;
            }
        }

        #endregion

        #region 内训课件

        /// <summary>
        /// 获取课件信息
        /// </summary>
        /// <param name="courseWareId"></param>
        /// <returns></returns>
        public CourseWareModel GetCourseWareInfoById(int courseWareId)
        {
            CourseWareModel model = new CourseWareModel();
            string sql = "SELECT ID,CourseID,SerialNumber,Content,CourseName,ProjectName,ImgUrl,SoundUrl,VideoUrl,ImgName,SoundName,VideoName FROM v_courseware WHERE ID = " + courseWareId;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.SerialNumber = (int)dr["SerialNumber"];
                    model.Content = dr["Content"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.ProjectName = dr["ProjectName"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.VideoUrl = dr["VideoUrl"].ToString();
                    model.ImgName = dr["ImgName"].ToString();
                    model.SoundName = dr["SoundName"].ToString();
                    model.VideoName = dr["VideoName"].ToString();
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 获取课件视频地址
        /// </summary>
        /// <param name="courseWareId"></param>
        /// <returns></returns>
        public string GetVideoUrlById(int id)
        {
            CourseWareModel model = new CourseWareModel();
            string sql = "SELECT VideoUrl FROM E_CourseWare WHERE ID = " + id;
            object rst = SqlHelper.ExecuteScalarBySql(sql);
            return rst == null ? "" : rst.ToString();
        }

        /// <summary>
        /// 获取课件信息（列表）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<CourseWareModel> GetCourseWareInfoById(string[] id)
        {
            IList<CourseWareModel> list = new List<CourseWareModel>();
            string sql = "SELECT ID,CourseID,SerialNumber,ImgUrl,SoundUrl,VideoUrl,ImgName,SoundName,VideoName FROM E_CourseWare WHERE " + DALUtils.GetInCondition("ID", id) + " ORDER BY SerialNumber DESC";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    CourseWareModel model = new CourseWareModel();
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.SerialNumber = (int)dr["SerialNumber"];
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.VideoUrl = dr["VideoUrl"].ToString();
                    model.ImgName = dr["ImgName"].ToString();
                    model.SoundName = dr["SoundName"].ToString();
                    model.VideoName = dr["VideoName"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 添加课件
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddCourseWare(CourseWareModel model)
        {
            IDbDataParameter[] parms = new IDbDataParameter[] { 
                new SqlParameter("@CourseID", model.CourseID),
                new SqlParameter("@SerialNumber", model.SerialNumber),
                new SqlParameter("@Content", model.Content),
                new SqlParameter("@ImgUrl", model.ImgUrl),
                new SqlParameter("@SoundUrl", model.SoundUrl),
                new SqlParameter("@VideoUrl", model.VideoUrl),
                new SqlParameter("@ImgName", model.ImgName),
                new SqlParameter("@SoundName", model.SoundName),
                new SqlParameter("@VideoName", model.VideoName)
            };
            //string sql = "UPDATE E_CourseWare SET SerialNumber = SerialNumber + 1 WHERE CourseID = @CourseID AND SerialNumber >= @SerialNumber; INSERT INTO E_CourseWare(CourseID,SerialNumber,Content,ImgUrl,SoundUrl,VideoUrl)VALUES(@CourseID,@SerialNumber,@Content,@ImgUrl,@SoundUrl,@VideoUrl)";
            //return SqlHelper.ExecuteNonQueryBySql(sql, parms);
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_internal_addCourseWare", parms);
        }

        /// <summary>
        /// 更新课件
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateCourseWare(CourseWareModel model)
        {
            IDbDataParameter[] parms = new IDbDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@CourseID", model.CourseID),
                new SqlParameter("@SerialNumber", model.SerialNumber),
                new SqlParameter("@OriginalSerialNumber", model.OriginalSerialNumber),
                new SqlParameter("@Content", model.Content),
                new SqlParameter("@ImgUrl", model.ImgUrl),
                new SqlParameter("@SoundUrl", model.SoundUrl),
                new SqlParameter("@VideoUrl", model.VideoUrl),
                new SqlParameter("@ImgName", model.ImgName),
                new SqlParameter("@SoundName", model.SoundName),
                new SqlParameter("@VideoName", model.VideoName)
            };
            string sql = "";

            //if (model.SerialNumber >= model.OriginalSerialNumber)
            //{
            //    if (model.SerialNumber > model.OriginalSerialNumber)
            //    {
            //        sql += "UPDATE E_CourseWare SET SerialNumber = SerialNumber - 1 WHERE CourseID = @CourseID AND SerialNumber <= @SerialNumber AND SerialNumber > @OriginalSerialNumber AND ID <> @ID;";
            //    }
            //}
            //else
            //{
            //    sql += "UPDATE E_CourseWare SET SerialNumber = SerialNumber + 1 WHERE CourseID = @CourseID AND SerialNumber Between @SerialNumber AND @OriginalSerialNumber AND ID <> @ID;";
            //}

            sql += " UPDATE E_CourseWare SET CourseID = @CourseID, SerialNumber = @SerialNumber, Content = @Content, ImgUrl = @ImgUrl, SoundUrl = @SoundUrl, VideoUrl = @VideoUrl,ImgName = @ImgName, SoundName = @SoundName, VideoName = @VideoName WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取课件列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public CourseWareListModel GetCourseWareListByCondition(SearchCourseWareModel searchModel, int pageSize, int pageIndex)
        {
            CourseWareListModel listModel = new CourseWareListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@ProjectID", searchModel.ProjectID),
                new SqlParameter("@TeacherID", searchModel.TeacherID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortCourseWareModel> list = new List<ShortCourseWareModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_getCourseWareListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortCourseWareModel shortModel = new ShortCourseWareModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.CourseName = dr["CourseName"].ToString();
                        shortModel.ProjectName = dr["ProjectName"].ToString();
                        shortModel.SerialNumber = (int)dr["SerialNumber"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取课件列表
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<CourseWareModel> GetCourseWareListByCourseID(int courseid)
        {
            IList<CourseWareModel> list = new List<CourseWareModel>();
            string sql = "SELECT ID, SerialNumber, Content, ImgUrl, SoundUrl, VideoUrl FROM E_CourseWare WHERE CourseID = " + courseid + " ORDER BY SerialNumber, ID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    CourseWareModel model = new CourseWareModel();
                    model.ID = (int)dr["ID"];
                    model.SerialNumber = (int)dr["SerialNumber"];
                    model.Content = dr["Content"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.VideoUrl = dr["VideoUrl"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 删除课件
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCourseWare(int id)
        {
            string sql = "DELETE FROM E_CourseWare WHERE ID = " + id;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 更新页码（删除某一页时）
        /// </summary>
        /// <param name="startNumber"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int UpdateSerialNumber(int startNumber, int courseid)
        {
            string sql = "UPDATE E_CourseWare SET SerialNumber = SerialNumber - 1 WHERE CourseID = " + courseid + " AND SerialNumber > " + startNumber;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 获取课件总数
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int GetCourseWarePageCount(int courseid)
        {
            //string sql = "SELECT COUNT(ID) AS Expr1 FROM E_CourseWare WHERE (CourseID = " + courseid + ")";
            string sql = "SELECT [SerialNumber] FROM E_CourseWare WHERE (CourseID = " + courseid + ")";
            object count = SqlHelper.ExecuteScalarBySql(sql);
            return count == null ? 0 : (int)count;
        }

        #endregion

        #region BookMark

        /// <summary>
        /// 获取用户书签
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int GetUserBookMark(int userid, int courseid)
        {
            int pageNumber = 0;
            string sql = "SELECT PageNumber FROM E_BookMark WHERE UserID = " + userid + " AND CourseID = " + courseid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    pageNumber = (int)dr[0];
                }
                return pageNumber;
            }
        }

        /// <summary>
        /// 书签处理
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="courseid"></param>
        /// <param name="pageNumber"></param>
        /// <returns></returns>
        public int BookMarkHandle(int userid, int courseid, int pageNumber)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserID", userid),
                new SqlParameter("@CourseID", courseid),
                new SqlParameter("@PageNumber", pageNumber)
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_handleBookMark", parms);
        }

        #endregion

    }
}
